﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Ylh.DBUtility;
using Ylh.IDAL;
using Ylh.Model;
using System.Data;
using System.Data.SqlClient;
namespace Ylh.SQLServerDAL
{
    public class RoleDAL : IRole
    {
        /// <summary>
        /// 执行插入角色,返回插入后的的角色ID
        /// </summary>
        /// <param name="ConStr">指定的数据库</param>
        /// <param name="RoleName">角色名称</param>
        /// <param name="Remark">角色备注</param>
        /// <param name="Remark">创建人</param>
        /// <returns>角色ID</returns>
        public int InsertRole(string ConStr, string RoleName, string Remark, string Creater, string ProtectCount)
        {
            string sql = @"insert INTO TbRole(RoleName,Remark,Creater,ProtectCount) VALUES(@RoleName,@Remark,@Creater,@ProtectCount) SELECT @@identity";
            SqlParameter[] pre = new SqlParameter[] 
            {
                 new SqlParameter ("@RoleName",RoleName),
              new SqlParameter ("@Remark",Remark),
              new SqlParameter("@Creater",@Creater),
               new SqlParameter("@ProtectCount",ProtectCount)
             };
            DataTable dt= SqlHelper.ExcuteQuery(ConStr, sql, pre, CommandType.Text);
            return Convert.ToInt32( dt.Rows[0][0]);
        }



        /// <summary>
        /// 更新角色信息
        /// </summary>
        /// <param name="ConStr">指定的数据库</param>
        /// <param name="RoleName">角色名称</param>
        /// <param name="Remark">角色备注</param>
        /// <param name="RoleID">角色编号</param>
        /// <returns>是否更新成功</returns>
        public bool UpdateRole(String ConStr,string RoleName, string Remark, decimal RoleID, string ProtectCount)
        {
            string sql = @"Update TbRole Set RoleName=@RoleName, Remark=@Remark ,ProtectCount=@ProtectCount  where RoleID=" + RoleID;
            SqlParameter[] pre = new SqlParameter[] 
            {
                  new SqlParameter ("@RoleName",RoleName),
              new SqlParameter ("@Remark",Remark),
              new SqlParameter ("@ProtectCount",ProtectCount)
             };
            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);
            if (i > 0) return true;
            return false;
        }



        /// <summary>
        /// 判断该名称是否存在
        /// </summary>
        /// <param name="ConStr">指定的数据库</param>
        /// <param name="RoleName">角色名称</param>
        /// <returns></returns>
        public bool IsExistsRole(string ConStr, string RoleName)
        {
            string sql = "SELECT RoleId from TbRole where RoleName=@RoleName";

            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@RoleName",RoleName)
            };

            DataTable dt = SqlHelper.ExcuteQuery(ConStr, sql, pre, CommandType.Text);

            if (dt.Rows.Count > 0) return true;
            return false;
        }


        /// <summary>
        /// 获取对应的角色信息
        /// 如果RoleID等于-1是获取所有信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="RoleID"></param>
        /// <returns></returns>
        public DataTable GetRoleInFo(string ConStr, decimal RoleID)
        {
            string sql = @"SELECT * from TbRole where 1=1";
            if (RoleID!=-1)
            {
                sql += "and RoleID="+RoleID;
            }
            return  SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }


        /// <summary>
        /// 根据编号删除角色
        /// 顺便删除编号下面已经有的权限
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="RoleID">角色编号</param>
        /// <returns></returns>
        public bool DeleteRoleByRoleID(string ConStr, string RoleID)
        {
            string sql = @"DELETE TbComRole WHERE RoleID=" + RoleID;
            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, CommandType.Text);

            sql = @" DELETE TbRole WHERE RoleID=" + RoleID;
            i = SqlHelper.ExecuteNonQuery(ConStr, sql, CommandType.Text);


            if (i > 0) return true;
            return false;
        }

        /// <summary>
        /// 判断角色是否已经有员工使用了
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="RoleID">角色编号</param>
        /// <returns></returns>
        public bool IsUseByStaff(String ConStr, string RoleID)
        {
            string sql = @"SELECT * FROM TbStaff where RoleID=" + RoleID;
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
            if (dt.Rows.Count == 0) return true;
            return false;
        }




        public int GetProtectCount(string ConStr, string RoleID)
        {
            string sql = @"select ProtectCount from TbRole where RoleID=" + RoleID;
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);

            return Convert.ToInt32(dt.Rows[0][0]);
        }


    }
}
